﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_REF_SHOP_Select_Paging')
	BEGIN
		DROP Procedure usp_UPDMS_REF_SHOP_Select_Paging
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_REF_SHOP_Select_Paging
**	Desc : 활력업소를 조회한다.
**	Test Exec Query : Exec usp_UPDMS_REF_SHOP_Select_Paging
**	Called by : Ref_Dac_UPDMS_REF_SHOP.cs
**	Program ID : Ref1010m
**	Auth : 송시명
**	Date : 2013-03-21
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_REF_SHOP_Select_Paging]
@li_st_page int,
@li_ed_page int,
@ls_category nvarchar(5),
@ls_shop_nm nvarchar(50),
@ls_address nvarchar(200),
@ls_favorite_yn nvarchar(1),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT a.Seq,
       a.Category,
       a.Category_Nm,
       a.Shop_Id,
       a.Shop_Nm,
       a.Reserve_Phone,
       a.Address,
	   a.NaverMap_Link,
       a.Latitude,
       a.Longitude,
       a.Favorite_Yn,
	   a.Favorite_Yn_Icon,
       a.Remark
  FROM (
       SELECT ROW_NUMBER() OVER(ORDER BY Favorite_Yn DESC, Shop_Nm ASC) AS Rnum,
              Seq,
              Category,
			  dbo.ufn_UPDMS_Get_Code_Name(Category, 'REF', @ls_lang_set) AS Category_Nm,
              Shop_Id,
              Shop_Nm,
              Reserve_Phone,
              Address,
			  CASE Latitude + Longitude WHEN '' THEN ''
                   ELSE '<div title="네이버맵" class="sbtn sbtn_map" onclick="goNaverMap(''' + Latitude + ''',''' + Longitude + ''');" />'
              END NaverMap_Link,
              Latitude,
              Longitude,
              Favorite_Yn,
              '<div class="sbtn sbtn_favorite_' + LOWER(Favorite_Yn) + '" title="즐겨찾기' + (CASE WHEN Favorite_Yn = 'Y' THEN '해제' ELSE '설정' END ) + '" onclick="javascript:setFavorite(''' + CONVERT(NVARCHAR, Seq) + ''');" />' AS Favorite_Yn_Icon,
              Remark
         FROM UPDMS_REF_SHOP WITH(NOLOCK)
        WHERE Category LIKE @ls_category + '%'
          AND Shop_Nm LIKE '%' + @ls_shop_nm + '%'
          AND (Address LIKE '%' + @ls_address + '%'
              OR Reserve_Phone LIKE '%' + @ls_address + '%'
              )
          AND Favorite_Yn LIKE @ls_favorite_yn + '%'
       ) a
 WHERE a.Rnum > @li_st_page AND a.Rnum <= @li_ed_page

GO